I've been in my current software company for over a decade, using their internal processes to access data. At my previous jobs (and personal projects), I was very fond of using the Dapper MicroORM library to do all of my SQL transactions. For migrating database definitions, my go-to library has always been FluentMigrator. Over time I drifted away from those libraries into other things until I had a lightbulb moment while watching a YouTube video from Dev Leader titled "I Choose THIS Over EF Core - How To Use Dapper in C#."
In that video, he goes over getting away from using Entity Framework Core and using Dapper to get a better handle on what your SQL is doing. He doesn't show how to use Dapper in .NET Aspire and he doesn't cover migrations that EF Core does pretty well. I had just setup EF Core and migrations in a personal project I'm working on when I had this zany idea - what if I nuked that and started over with Dapper and FluentMigrator? Let's do it!
One thing to consider is that EF Core's migration project that I detailed in my last post created the target database during the migration, so we'll need to keep this in mind. If we pass the database reference from the AppHost to the Migrations project, it will give us a connectionstring to the database that doesn't exist yet. We'll want to do something like this:
var dbserver = builder.AddPostgres("dbserver")
.WithPgAdmin();
var theDb = dbserver.AddDatabase("thedb");
builder.AddProject<Projects.MigrationService>("migration")
.WithReference(dbserver)
.WithReference(theDb);
With our new WithReference arguments added to the AddProject, let's setup the FluentMigrator to do its thing:
using MigrationService;
using MigrationService.Migrations;
using FluentMigrator.Runner;
var builder = Host.CreateApplicationBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("thedb");
builder.Services.AddFluentMigratorCore()
.ConfigureRunner(rb => rb
// Define the assembly containing your migrations
.AddPostgres()
// Set the connection string
.WithGlobalConnectionString(connectionString)
// Define the schema that contains the version table - I'm referencing the initial first migration we're doing
.ScanIn(typeof(CreateUserTable).Assembly).For.Migrations())
// Enable logging to console in the FluentMigrator way
.AddLogging(lb => lb.AddFluentMigratorConsole());
builder.Services.AddHostedService<ApiDbInitializer>();
var app = builder.Build();
app.Run();
What do we put in the ApiDbInitializer now instead of the EF Core code that existed previously? Here's what it looks like now:
using System.Diagnostics;
using FluentMigrator.Runner;
using Npgsql;
namespace MigrationService;
public class ApiDbInitializer(
IServiceProvider serviceProvider,
IHostApplicationLifetime hostApplicationLifetime,
IMigrationRunner migrationRunner,
IConfiguration configuration) : BackgroundService
{
private const string ActivitySourceName = "Migrations";
private static readonly ActivitySource SActivitySource = new(ActivitySourceName);
protected override async Task ExecuteAsync(CancellationToken cancellationToken)
{
using var activity = SActivitySource.StartActivity("Migrating database", ActivityKind.Client);
try
{
using var scope = serviceProvider.CreateScope();
await EnsureDatabaseExistsAsync(cancellationToken);
migrationRunner.MigrateUp();
activity?.SetStatus(ActivityStatusCode.Ok);
}
catch (Exception ex)
{
activity?.SetStatus(ActivityStatusCode.Error, ex.ToString());
throw;
}
hostApplicationLifetime.StopApplication();
}
private async Task EnsureDatabaseExistsAsync(CancellationToken cancellationToken)
{
var databaseName = "testdb";
/// This comes from Aspire's AppHost
var connectionString = configuration.GetConnectionString("dbserver");
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);
var command = new NpgsqlCommand($"SELECT 1 FROM pg_database WHERE datname='{databaseName}'", connection);
var result = await command.ExecuteScalarAsync(cancellationToken);
if (result == null)
{
command = new NpgsqlCommand($"CREATE DATABASE {databaseName}", connection);
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
}
Now that we've created our empty database on our Aspire pop-up Postgresql server, let's create the Users table. I'm keeping all of the migration classes in a Migrations folder:
namespace MigrationService.Migrations;
public class CreateUserTable
{
[Migration(1)]
public class AddUsersTable : Migration
{
public override void Up()
{
Create.Schema("Users");
Create.Table("Users")
.InSchema("Users")
.WithColumn("Id ").AsGuid().PrimaryKey()
.WithColumn("TelegramId").AsInt64()
.WithColumn("FirstName").AsString()
.WithColumn("LastName").AsString()
.WithColumn("UserName").AsString()
.WithColumn("LanguageCode").AsString()
.WithColumn("IsPremium").AsBoolean()
.WithColumn("Status").AsString()
.WithColumn("UserTimeZone").AsString()
.WithColumn("Created").AsDateTimeOffset();
Create.Index("IX_Users_TelegramId")
.OnTable("Users")
.InSchema("Users")
.OnColumn("TelegramId");
}
public override void Down()
{
Delete.Table("Users");
}
}
}
The only thing left to do for now is to create the test data. I created the first dynamic object in the following code. I asked JetBrains Rider AI Assistant to create more test data for me. It did a fabulous job and saved me from having to type a bunch of information into the dynamic objects:
namespace MigrationService.Migrations;
public class CreateUserTestTable
{
[Profile("Development")]
[Migration(2)]
public class AddUsersTestData : Migration
{
public override void Up()
{ Insert.IntoTable("Users")
.InSchema("Users")
.Row(new
{
Id = new Guid("12345678-1234-1234-1234-1234567890ab"),
Created = new DateTimeOffset(
new DateTime(2024, 5, 25, 14, 21, 14, 204, DateTimeKind.Unspecified).AddTicks(5510),
new TimeSpan(0, -4, 0, 0, 0)),
FirstName = "Sam",
IsPremium = false,
LanguageCode = "en-US",
LastName = "Smith",
Status = "New",
TelegramId = 123456789L,
UserName = "samsmith",
UserTimeZone = "Local"
})
.Row(new
{
Id = new Guid("23456789-2345-2345-2345-2345678901bc"),
Created = new DateTimeOffset(
new DateTime(2024, 5, 26, 14, 21, 14, 204, DateTimeKind.Unspecified).AddTicks(5510),
new TimeSpan(0, -4, 0, 0, 0)),
FirstName = "John",
IsPremium = true,
LanguageCode = "en-GB",
LastName = "Doe",
Status = "Active",
TelegramId = 234567890L,
UserName = "johndoe",
UserTimeZone = "GMT"
})
.Row(new
{
Id = new Guid("34567890-3456-3456-3456-3456789012cd"),
Created = new DateTimeOffset(
new DateTime(2024, 5, 27, 14, 21, 14, 204, DateTimeKind.Unspecified).AddTicks(5510),
new TimeSpan(0, -4, 0, 0, 0)),
FirstName = "Jane",
IsPremium = false,
LanguageCode = "en-US",
LastName = "Doe",
Status = "Inactive",
TelegramId = 345678901L,
UserName = "janedoe",
UserTimeZone = "Local"
});
}
public override void Down()
{
Delete.FromTable("Users").InSchema("Users").AllRows();
}
}
}
The code is ready to spool up a new PostgreSQL database, create a table and inject some test data. You'll need to make the AppHost project the starting project to make this work. Once it has started and starts up the other projects, it will inject the dbserver and theDb settings into the builder.Configuration for us to use. This is great as Aspire (by default) starts up the projects using different ports, usernames and passwords. We will just take what Aspire gives us for those pieces of information and inject it into our Migration service.
The service starts per run of your AppHost and then, unlike the other Projects in the Solution, you should see it start and then change to Finished when it's done the migration. If you requested that PgAdmin be loaded with your solution, you can use it to see that in fact the Users table was created and filled in with test data.
In a future post I'm going to delve into testing the API with the test database and data. Aspire provides a test project but when I initially tried to use it to test the API, it didn't work. Once I figure things out I'll share it with you!